﻿CREATE PROCEDURE [dbo].[utl_CreateSPScriptTable]

AS
BEGIN
	DECLARE @True  AS BIT; SET @True  = 1
	DECLARE @False AS BIT; SET @False = 0
	
	DECLARE @ErrorCode     INT
	DECLARE @RowsAffected  INT
	DECLARE @Message       VARCHAR(MAX)
	
	-- see if CreateSPScriptTable already exists.
	IF dbo.fn_TableExists('CreateSPScriptTable') = @True
	BEGIN
		TRUNCATE TABLE dbo.CreateSPScriptTable
	END
	ELSE
	BEGIN
	    -- create CreateSPScriptTable
	    CREATE TABLE dbo.CreateSPScriptTable
	    (
	    	ID    INT IDENTITY(1, 1) NOT NULL,
	    	Line  NVARCHAR(MAX) NOT NULL
	    )

	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT

	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Unable to create table ''CreateSPScriptTable''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 1
	    END

		-- add a primary key
		ALTER TABLE dbo.CreateSPScriptTable /*WITH NOCHECK*/ 
			ADD CONSTRAINT PK_CreateSPScriptTable PRIMARY KEY CLUSTERED(ID)

		SELECT @ErrorCode = @@ERROR,
			   @RowsAffected = @@ROWCOUNT

		IF @ErrorCode <> 0
		BEGIN
			SET @Message = 'Unable to create primary key for table ''CreateSPScriptTable''.'
			RAISERROR(@Message, 16, 1)
			RETURN 1
		END
	END
	
	INSERT INTO dbo.CreateSPScriptTable
	  (
	    Line
	  )
	VALUES
	  (
	    dbo.fn_SPScriptSeparator()
	  )

	INSERT INTO dbo.CreateSPScriptTable
	  (
	    Line
	  )
	VALUES
	  (
	    ''
	  )
		
	RETURN 0
END

